package henu.dao.impl;

import henu.bean.Questions;
import henu.dao.QuestionsDao;
import henu.dao.factory.DaoFactory;
import henu.util.Dbcp;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

public class QuestionsDaoImpl implements QuestionsDao {

	public static void main(String[] args) {
		QuestionsDaoImpl dap = new QuestionsDaoImpl();
		Questions q = new Questions();
		q.setAnswer("answer");
		q.setContent("content");
		q.setOptiona("a");
		q.setOptionb("b");
		q.setOptionc("c");
		q.setOptiond("d");
		q.setRemark("beizhu");
		q.setSubject("sub");
		q.setType("type");
		dap.save(q);
	}

	/**
	 * @param sql
	 *            查询语句
	 * @param param
	 *            查询的参数
	 * @param result
	 *            返回操作成功的个数
	 * */
	@Override
	public int save(Questions ques) {
		// Questions ques = new Questions();
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		int result = 0;
		// INSERT INTO Questions VALUES(1,'最好的编程语言是什么?','java','常识','基本');
		String sql = "INSERT INTO Questions(content,subject,type,optiona,optionb,optionc,optiond,answer,remark) VALUES(?,?,?,?,?,?,?,?,?)";
		Object[] params = {ques.getContent(),ques.getSubject(),ques.getType() ,ques.getOptiona(),ques.getOptionb(),ques.getOptionc(),
				ques.getOptiond(),ques.getAnswer(),ques.getRemark()};
		try {
			result = runner.update(sql, params);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		return result;
	}
	@Override
	public int delete(String id) {
		// DELETE FROM Questions WHERE qid=1 ;
		String sql = "DELETE FROM Questions WHERE qid=?";
		int result = 0;
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			result = runner.update(sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public Questions findById(String id) {
		// 此处返回是bean ,查出来的所有值赋值给bean然后把bean返回
		Questions ques = null;
		String sql = "select * from Questions where qid=?";
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			ques = runner.query(sql,
					new BeanHandler<Questions>(Questions.class), id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ques;
	}

	@Override
	public List<Questions> findAll(String order, String sort, String property,
			String key) {
		// SELECT * FROM Questions GROUP BY content ORDER BY content DESC ;
		List<Questions> list = null;
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		String sql = "SELECT * FROM Questions where " + property + " = ? "
				+ " ORDER BY  " + order + " " + sort;
		// System.out.println("final-sql:"+sql);
		try {
			list = runner.query(sql, new BeanListHandler<Questions>(
					Questions.class), key);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 真正的模糊查询 ---此处 加入是类型 或者科目这都是分类的标准 所以不允许模糊查询 假如 是内容和答案就进行模糊查询
	 * 
	 * @param property
	 *            要查询的字段名称
	 * @param key
	 * @param order
	 *            排序字段
	 * @param sort
	 *            排序方式
	 * @return
	 */
	@Override
	public List<Questions> findByProperty(String property, String key,
			String order, String sort, int start, int end) {
		List<Questions> list = null;
		String sql = "SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from Questions where "
				+ property
				+ "='"
				+ key
				+ "'  order by "
				+ order
				+ " "
				+ sort
				+ ") tt WHERE ROWNUM <=" + end + ") WHERE ro > " + start + "";
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			list = runner.query(sql, new BeanListHandler<Questions>(
					Questions.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;

	}

	/*
	 * 这个方法要求的是根据id 这个可以理解为随机获取一张卷子
	 */
	@Override
	public List<Questions> getQuestions(String[] id) {
		// findById();
		List<Questions> list = new ArrayList<Questions>();
		for (int i = 0; i < id.length; i++) {
			list.add(findById(id[i]));
		}
		return list;
	}

	@Override
	public int update(String id, Questions ques) {
		// UPDATE Questions SET content ='河大建校时间' ,answer ='1912'
		// ,SUBJECT='语文',TYPE='常识' WHERE qid=1 ;
		String sql = "UPDATE  Questions SET content =?,answer=? ,SUBJECT=?,TYPE=?,OPTIONA =? , OPTIONB =? , OPTIONC =? , OPTIOND =?  WHERE qid="
				+ id;
		// System.out.println("sql:"+sql);
		int result = 0;
		Object[] params = { ques.getContent(), ques.getAnswer(),
				ques.getSubject(), ques.getType(), ques.getOptiona(),
				ques.getOptionb(), ques.getOptionc(), ques.getOptiond() };
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			result = runner.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public List<String> findAllSub(){
		List<String> allsub = new ArrayList<String>();
		QueryRunner runner = DaoFactory.getRunner();		//获取runner
		List<Object[]> queryresult = null ; 
		String sql="SELECT DISTINCT(subject) from QUESTIONS" ;
		try {
			queryresult = runner.query(sql, new ArrayListHandler());
			//每行都取第一列的值就ok
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		for(Object[] ob:queryresult){
			for(Object o :ob){
				//此处的o就是查询出来的值
				allsub.add(String.valueOf(o));
				//System.out.println("科目："+o.toString() );
			}
		}
		return allsub;
	}
	/**
	 * 根据属性查找试题
	 * @param property1  属性1
	 * @param key1  关键字1
	 * @param property2  属性2
	 * @param key2  关键字2
	 * @return 符合查询要求的试题
	 * */
	@Override
	public List<Questions> status(String property1, String key1,
			String property2, String key2) {
			// SELECT * FROM Questions GROUP BY content ORDER BY content DESC ;
			List<Questions> list = null;
			QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
			String sql = "SELECT * FROM Questions where " + property1 + " = ? and " +property2 + " = ? ";
			try {
				list = runner.query(sql, new BeanListHandler<Questions>(
						Questions.class), key1,key2);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return list;
	}
}
